package com.gjm.tool.utils;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

public class SqlTest {
    public static void main(String[] args) throws Exception{
        String baseIndex = "select DBMS_METADATA.GET_DDL('INDEX', index_name,'BILLING' ) || ';' from dba_indexes where table_name='%s' and owner='BILLING' AND INDEX_NAME NOT LIKE 'SYS' ";
        List<String> tableList = FileUtil.readInfoFromFile("C:\\Users\\Administrator\\Desktop\\alltable.txt");

        for (String table : tableList) {
            System.out.println(String.format(baseIndex,table).replace("SYS","SYS%"));

            System.out.println("union all ");
        }
    }

    public static void indexCreate() throws Exception{
        String baseIndex = "CREATE INDEX %s ON %s (字段名);";
        List<IndexVO> list = index();
        List<String> tableList = FileUtil.readInfoFromFile("C:\\Users\\Administrator\\Desktop\\alltable.txt");

        for (String table : tableList) {

            List<IndexVO> indexList = list.stream().filter(i -> i.getTABLE_NAME().equals(table)).collect(Collectors.toList());
            if (CollectionUtil.isEmpty(indexList)){
                continue;
            }
            System.out.println("");
            for (IndexVO indexVO : indexList) {
                System.out.println(String.format(baseIndex,indexVO.getINDEX_NAME(),indexVO.getTABLE_NAME()));
            }
            System.out.println("");
        }
    }

    public static void createTable() throws Exception{

        String baseSql = "CREATE table billing.%s AS SELECT * FROM billing.%s WHERE 1=0;";

        List<String> list = FileUtil.readInfoFromFile("C:\\Users\\Administrator\\Desktop\\alltable.txt");
        for (String str : list) {
            if (StrUtil.isNotBlank(str)){
                String newName = str.replace("202412", "202501");
                newName = newName.replace("2024", "2025");
                System.out.println(String.format(baseSql,newName,str));
            }
        }
    }


    public static List<IndexVO> index() {
        String filePath = "C:\\Users\\Administrator\\Desktop\\11.xls";
        ExcelReader reader = ExcelUtil.getReader(filePath);
        // 读取指定sheet页面，0表示第一个sheet
        List<List<Object>> sheetList = reader.read(0);
        // 关闭读取器，释放资源
        reader.close();
        List<IndexVO> list = new ArrayList<>();
        for (int i = 1; i < sheetList.size(); i++) {
            List<Object> line = sheetList.get(i);
            list.add(new IndexVO(String.valueOf(line.get(0)),String.valueOf(line.get(1)),String.valueOf(line.get(2))));
        }
        return list;
    }

    @AllArgsConstructor
    @NoArgsConstructor
    @Data
    static class IndexVO {
        private String OWNER;
        private String TABLE_NAME;
        private String INDEX_NAME;
    }
}
